<%@ page import="org.bson.Document" %>
<%@ page import="com.mongodb.client.*" %>
<%@ page import="org.bson.conversions.Bson" %>
<%@ page import="org.apache.commons.fileupload.servlet.ServletFileUpload" %>
<%@ page import="org.apache.commons.fileupload.FileItem" %>
<%@ page import="org.apache.commons.fileupload.FileItemFactory" %>
<%@ page import="org.apache.commons.fileupload.disk.DiskFileItemFactory" %>
<%@ page import="java.io.File" %>
<%@ page import="cn.hutool.poi.excel.ExcelReader" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page import="java.util.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%
    request.setCharacterEncoding("utf-8");
    if (ServletFileUpload.isMultipartContent(request) && "POST".equals(request.getMethod())) {
        FileItemFactory factory = new DiskFileItemFactory();
        ServletFileUpload upload = new ServletFileUpload(factory);
        List<FileItem> fileItems = upload.parseRequest(request);
        for (FileItem item : fileItems) {
            if (!item.isFormField()) {
                if (!item.getName().endsWith(".xlsx")) {
                    response.getWriter().write("<script language=JavaScript>{window.alert('请上传excel文件!');window.location.href='index.jsp'}</script>");
                    return;
                }

                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

                ExcelReader reader = new ExcelReader(item.getInputStream(), 0);
                List<Map<String, Object>> list = reader.readAll();
                if (list.isEmpty()) {
                    response.getWriter().write("<script language=JavaScript>{window.alert('上传失败，请不要上传空文件!');window.location.href='index.jsp'}</script>");
                    return;
                }
                //连接mongodb
                String url = "mongodb://g08:abc%408@mongo.zhledu.cn:27017/g08";
                MongoClient client = MongoClients.create(url);
                MongoIterable<String> names = client.listDatabaseNames();
                MongoDatabase g08 = client.getDatabase("g08");
                MongoCollection<Document> collection = g08.getCollection("student");

                //获取到最大的id
                List<Bson> idQueryList = new LinkedList<>();
                Document idGroup = new Document();
                idGroup.append("$group", new Document("_id", 1).append("max", new Document("$max", "$_id")));
                idQueryList.add(idGroup);
                final AggregateIterable<Document> maxId = collection.aggregate(idQueryList);
                int id = 0;
                for (Document d : maxId) {
                    id = d.getInteger("max") + 1;
                    break;
                }

                //获取最大的学号
                List<Bson> snoQueryList = new LinkedList<>();
                Document snoGroup = new Document();
                snoGroup.append("$group", new Document("_id", 1).append("max", new Document("$max", "$sno")));
                snoQueryList.add(snoGroup);
                final AggregateIterable<Document> maxSno = collection.aggregate(snoQueryList);
                int sno = 0;
                for (Document d : maxSno) {
                    sno = Integer.parseInt(d.getString("max")) + 1;
                    break;
                }

                List<Document> studentList = new ArrayList<>();
                for (Map<String, Object> map : list) {
                    Document student = new Document().append("_id", id++).append("sno", String.valueOf(sno++));
                    for (String key : map.keySet()) {
                        if ("姓名".equals(key)) {
                            student.append("name", map.get(key).toString());
                        }
                        if ("性别".equals(key)) {
                            student.append("sex", map.get(key).toString());
                        }
                        if ("年龄".equals(key)) {
                            student.append("age", Integer.parseInt(map.get(key).toString()));
                        }


                        if ("出生日期".equals(key)) {
                            Date date = sdf.parse(map.get(key).toString());
                            student.append("birthDay", sdf.format(date));
                        }
                        if ("身份证号".equals(key)) {
                            student.append("idCard", map.get(key).toString());
                        }
                        if ("专业".equals(key)) {
                            student.append("major", map.get(key).toString());
                        }
                        if ("用户名".equals(key)) {
                            student.append("username", map.get(key).toString());
                        }
                        if ("密码".equals(key)) {
                            student.append("password", map.get(key).toString());
                        }
                        if ("电话号码".equals(key)) {
                            student.append("phone", map.get(key).toString());
                        }
                        if ("电子邮件".equals(key)) {
                            student.append("email", map.get(key).toString());
                        }
                        if ("住址".equals(key)) {
                            student.append("address", map.get(key).toString());
                        }
                    }
                    studentList.add(student);
                }

                collection.insertMany(studentList);
                response.getWriter().write("<script language=JavaScript>{window.alert('导入成功!');window.location.href='index.jsp'}</script>");
                break;
            }
        }
    } else {
        response.getWriter().write("<script language=JavaScript>{window.alert('表单设置错误，请设计用POST方法和multipart/form-data方式提交表单!');window.location.href='index.jsp'}</script>");

    }


%>
